package com.example.week14.dao.impl;

import com.example.week14.dao.StudentDao;
import com.example.week14.entity.Student;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Map;

public class StudentDaoImpl implements StudentDao {

    private Connection conn;//数据库连接对象
    private PreparedStatement pstmt;//数据库的操作对象
    // 构造方法，接受一个数据库连接对象作为参数
    public StudentDaoImpl(Connection conn) {
        this.conn = conn;
    }

    // 插入单个学生信息
    @Override
    public int insert(Student student) throws Exception {
        // SQL 插入语句
        String sql = "INSERT ignore INTO student (sno, name, age, sex, tel, photo) VALUES (?, ?, ?, ?, ?, ?)";
        // 使用 PreparedStatement 预编译 SQL 语句，防止 SQL 注入
        pstmt = conn.prepareStatement(sql);
        // 设置参数值
        pstmt.setString(1, student.getSno());
        pstmt.setString(2, student.getName());
        pstmt.setString(3, student.getAge());
        pstmt.setString(4, student.getSex());
        pstmt.setString(5, student.getTel());
        pstmt.setString(6, student.getPhoto());
        // 执行 SQL 语句
        return pstmt.executeUpdate();
    }

    // 批量插入学生信息
    @Override
    public int insertBatch(List<Student> students) throws
            Exception {
        // SQL 插入语句
        String sql = "INSERT IGNORE INTO student (sno, name, age, sex, tel, photo) VALUES (?, ?, ?, ?, ?, ?)";
        // 使用 PreparedStatement 预编译 SQL 语句，防止 SQL 注入
        pstmt = conn.prepareStatement(sql);
        // 设置批处理模式
        conn.setAutoCommit(false);
        // 遍历学生列表，设置参数并添加到批处理中
        for (Student student : students) {
            pstmt.setString(1, student.getSno());
            pstmt.setString(2, student.getName());
            pstmt.setString(3, student.getAge());
            pstmt.setString(4, student.getSex());
            pstmt.setString(5, student.getTel());
            pstmt.setString(6, student.getPhoto());
            // 将当前语句添加到批处理中
            pstmt.addBatch();
        }
        // 执行批处理，如果某个插入语句执行成功并插入一行数据，则对应的数组元素为 1，否则为0
        int[] rowsAffectedArray = pstmt.executeBatch();
        // 提交事务
        conn.commit();
        conn.setAutoCommit(true);
        // 统计总影响行数
        int totalRowsAffected = 0;
        for (int rowsAffected : rowsAffectedArray) {
            totalRowsAffected += rowsAffected;
        }
        return totalRowsAffected;
    }

    @Override
    public int updateByPrimaryKey(Student student) throws
            Exception {
        String sql = "UPDATE student SET name=?, age=?, sex=?, tel=?, photo=? WHERE sno=?";
        int updateCount = 0;
        pstmt = conn.prepareStatement(sql);
        // 设置参数
        int index = 1;
        pstmt.setString(index++, student.getName());
        pstmt.setString(index++, student.getAge());
        pstmt.setString(index++, student.getSex());
        pstmt.setString(index++, student.getTel());
        pstmt.setString(index++, student.getPhoto());
        pstmt.setString(index++, student.getSno());
        // 执行更新操作
        updateCount = pstmt.executeUpdate();
        return updateCount;
    }

    //根据条件更新数据,接受一个条件和一个更新的参数列表
    @Override
    public int updateByCondition(String condition, Map<String, Object> updateParams) throws Exception {
        // 创建一个字符串构建器，用于动态构建SQL语句
        StringBuilder sql = new StringBuilder("UPDATE student SET ");
                // 参数索引，用于设置PreparedStatement的参数
        int paramIndex = 1;
        // 遍历更新参数映射，构建UPDATE语句的SET部分
        for (Map.Entry<String, Object> entry :
                updateParams.entrySet()) {
            sql.append(entry.getKey()).append("=?, ");
        }
        // 去掉SET部分最后多余的逗号
        sql.delete(sql.length() - 2, sql.length());
        // 在SQL语句末尾添加WHERE条件
        sql.append(" WHERE ").append(condition);
        // 初始化更新记录数
        int updateCount = 0;
        pstmt = conn.prepareStatement(sql.toString());
        // 遍历更新参数映射，为PreparedStatement设置参数
        for (Map.Entry<String, Object> entry :
                updateParams.entrySet()) {
            pstmt.setObject(paramIndex++, entry.getValue());
        }
        // 执行更新操作，并获取受影响的记录数
        updateCount = pstmt.executeUpdate();
        // 返回更新记录数
        return updateCount;
    }

    //根据主键删除单条数据
    @Override
    public int deleteByPrimaryKey(String sno) throws
            Exception {
        String sql = "DELETE FROM student WHERE sno = ?";
        pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, sno);
        return pstmt.executeUpdate();
    }
    
    //根据主键删除多条数据
    @Override
    public int deleteByPrimaryKey(List<String> snoList)
            throws Exception {
        if (snoList.isEmpty()) {
            return 0;  // 没有学号，不执行删除操作
        }
        // 构造 SQL 语句，根据 snoList 的大小生成对应数量的占位符
        String sql = "DELETE FROM student WHERE sno IN (" +
                String.join(",", Collections.nCopies(snoList.size(), "?")) + ")";
        pstmt = conn.prepareStatement(sql);
        // 设置参数值
        for (int i = 0; i < snoList.size(); i++) {
            pstmt.setString(i + 1, snoList.get(i));
        }
        return pstmt.executeUpdate();
    }

    //根据条件删除数据
    @Override
    public int deleteByCondition(String condition) throws
            Exception {
        String sql = "DELETE FROM student WHERE " + condition;
        pstmt = conn.prepareStatement(sql);
        return pstmt.executeUpdate();
    }

    //根据主键查询学生数据
    @Override
    public Student selectByPrimaryKey(String sno) throws
            Exception {
        String sql = "SELECT * FROM student WHERE sno = ?";
        pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, sno); // 设置参数，即学号
        ResultSet rs = pstmt.executeQuery(); // 执行查询，返回结果集
        if (rs.next()) {
            // 解析结果集，构建学生对象并返回
            return new Student(rs.getString(1),
                    rs.getString(2), rs.getString(3), rs.getString(4),
                    rs.getString(5), rs.getString(6));
        }
        return null; // 没有找到对应学号的学生，返回null
    }

    //查询所有数据
    @Override
    public List<Student> selectAll() throws Exception {
        String sql = "SELECT * FROM student";
        List<Student> studentList = new ArrayList<>();
        pstmt = conn.prepareStatement(sql);
        ResultSet rs = pstmt.executeQuery(); // 执行查询，返回结果集
        while (rs.next()) {
            // 解析结果集，构建学生对象并添加到列表中
            studentList.add(new Student(rs.getString(1),
                    rs.getString(2), rs.getString(3), rs.getString(4),
                    rs.getString(5), rs.getString(6)));
        }
        return studentList; // 返回学生列表
    }

    // 根据条件查询数据
    @Override
    public List<Student> selectByCondition(String condition)
            throws Exception {
        List<Student> studentList = new ArrayList<>();
        String sql = "SELECT * FROM student WHERE " + condition;
        pstmt = conn.prepareStatement(sql);
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
            studentList.add(new Student(rs.getString(1),
                    rs.getString(2), rs.getString(3), rs.getString(4),
                    rs.getString(5), rs.getString(6)));
        }
        return studentList;
    }

    @Override
    public List<Student> selectByPage(int pageIndex, int pageSize) throws Exception {
        // 计算偏移量
        int offset = (pageIndex - 1) * pageSize;
        // SQL 查询语句，使用 LIMIT 和 OFFSET 进行分页
        String sql = "SELECT * FROM student LIMIT ? OFFSET ?";
        pstmt = conn.prepareStatement(sql);
        // 设置参数
        pstmt.setInt(1, pageSize);
        pstmt.setInt(2, offset);
        // 执行查询，返回结果集
        ResultSet rs = pstmt.executeQuery();
        // 解析结果集，构建学生对象列表
        List<Student> studentList = new ArrayList<>();
        while (rs.next()) {
            studentList.add(new Student(
                    rs.getString("sno"),
                    rs.getString("name"),
                    rs.getString("age"),
                    rs.getString("sex"),
                    rs.getString("tel"),
                    rs.getString("photo")
            ));
        }
        return studentList;
    }

    //获取学生表中的总记录数。
    @Override
    public int getTotalRecords() throws Exception {
        // SQL 查询语句，统计学生表中的总记录数
        String sql = "SELECT COUNT(*) FROM student";
        try (PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            // 如果查询结果包含数据，返回第一列的值（总记录数）
            if (rs.next()) {
                return rs.getInt(1);
            }
        }
        // 如果没有数据或发生异常，返回0
        return 0;
    }

    // 检查学号是否存在
    public boolean isStudentIdExists(String studentId) throws SQLException {
        String sql = "SELECT COUNT(*) FROM student WHERE sno = ?";
        try (PreparedStatement preparedStatement = conn.prepareStatement(sql)) {
            preparedStatement.setString(1, studentId);
            ResultSet resultSet = preparedStatement.executeQuery();
            resultSet.next();
            int count = resultSet.getInt(1);
            return count > 0;
        }
    }

    @Override
    public List<Student> queryStudents(String studentId, String studentName, String studentAge, String studentSex, String studentTel) throws SQLException {
        List<Student> students = new ArrayList<>();
        // 构建 SQL 语句
        StringBuilder sql = new StringBuilder("SELECT * FROM student WHERE 1 = 1");

        if (studentId != null && !studentId.trim().isEmpty()) {
            sql.append(" AND sno = ?");
        }

        if (studentName != null && !studentName.trim().isEmpty()) {
            sql.append(" AND name LIKE ?");
        }

        if (studentAge != null && !studentAge.trim().isEmpty()) {
            sql.append(" AND age = ?");
        }

        if (studentSex != null && !studentSex.trim().isEmpty()) {
            sql.append(" AND sex = ?");
        }

        if (studentTel != null && !studentTel.trim().isEmpty()) {
            sql.append(" AND tel LIKE ?");
        }

        try (PreparedStatement statement = conn.prepareStatement(sql.toString())) {
            int parameterIndex = 1;

            if (studentId != null && !studentId.trim().isEmpty()) {
                statement.setString(parameterIndex++, studentId);
            }

            if (studentName != null && !studentName.trim().isEmpty()) {
                statement.setString(parameterIndex++, "%" + studentName + "%");
            }

            if (studentAge != null && !studentAge.trim().isEmpty()) {
                statement.setString(parameterIndex++, studentAge);
            }

            if (studentSex != null && !studentSex.trim().isEmpty()) {
                statement.setString(parameterIndex++, studentSex);
            }

            if (studentTel != null && !studentTel.trim().isEmpty()) {
                statement.setString(parameterIndex++, "%" + studentTel + "%");
            }

            // 执行查询
            try (ResultSet resultSet = statement.executeQuery()) {
                while (resultSet.next()) {
                    Student student = new Student(
                            resultSet.getString("sno"),
                            resultSet.getString("name"),
                            resultSet.getString("age"),
                            resultSet.getString("sex"),
                            resultSet.getString("tel"),
                            resultSet.getString("photo")
                    );
                    students.add(student);
                }
            }
        }
        return students;
    }


}
